<?xml version="1.0" ?>

<xsl:stylesheet   xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
						xmlns:lzc="http://schemas.icitadel.com/Lazarus/Compiled.xsd"
                  version="1.0">

   <xsl:output method="text"/>

	<xsl:include href="../../common.xsl" />
	<xsl:include href="../../convert.xsl" />
   
   <xsl:template match="lzc:project">
		<xsl:apply-templates select="lzc:security" />
   </xsl:template>
   
   <xsl:template match="lzc:security">
		<xsl:text>USE Master;

-- Creating Roles

</xsl:text>
		<xsl:for-each select="lzc:roles/lzc:role">
			<xsl:text>
PRINT 'Creating Role [</xsl:text><xsl:value-of select="@name" /><xsl:text>]';
EXEC sp_addrole '</xsl:text><xsl:value-of select="@name" /><xsl:text>';
GO
</xsl:text>
		</xsl:for-each>

<xsl:text>

-- Creating SQL Server Logins

</xsl:text>
		<xsl:for-each select="lzc:users/lzc:user[ @user-type = 'Internal' ]">
			<xsl:text>
PRINT 'Creating SQL Server Login [</xsl:text><xsl:value-of select="@name" /><xsl:text>]';
EXEC sp_addlogin '</xsl:text><xsl:value-of select="@name" /><xsl:text>', '</xsl:text><xsl:value-of select="@password" /><xsl:text>', '</xsl:text><xsl:value-of select="ancestor::lzc:project/lzc:datasource/@schema" /><xsl:text>';
GO
			</xsl:text>
		</xsl:for-each>
		
<xsl:text>

-- Granting SQL Server Database Access

</xsl:text>
		<xsl:for-each select="lzc:users/lzc:user">
			<xsl:text>
PRINT 'Creating SQL Server Login [</xsl:text><xsl:value-of select="@name" /><xsl:text>]';
EXEC sp_grantdbaccess '</xsl:text><xsl:value-of select="@name" /><xsl:text>';

GO
			</xsl:text>
		</xsl:for-each>
		
<xsl:text>

-- Assigning Database Roles

</xsl:text>
		<xsl:for-each select="lzc:users/lzc:user">
			<xsl:text>
PRINT 'Assigning Database Roles for User [</xsl:text><xsl:value-of select="@name" /><xsl:text>]';
</xsl:text>
			<xsl:for-each select="lzc:roles/lzc:role">
				<xsl:text>EXEC sp_addrolemember '</xsl:text><xsl:value-of select="@name" /><xsl:text>', '</xsl:text><xsl:value-of select="ancestor::lzc:user/@name" /><xsl:text>';
</xsl:text>			
			</xsl:for-each>
		</xsl:for-each>

GO
	</xsl:template>

</xsl:stylesheet>

